class Category < ActiveRecord::Base
  has_many :products,
    :conditions=>"DATE_ADD(`active_date`,INTERVAL `live_date` DAY) > now()",
    :order=>'sort_order desc'
  acts_as_tree :order=>"sort_order desc",:counter_cache => 'children_count'

  def self.parent_categories
    Category.find(:all,:conditions=>["parent_id is null"])
  end

  def self.find_by_parent_and_brand(brand_id,parent_id)
    self.find_by_sql(["SELECT DISTINCT c.*
FROM products p, categories c, brands b,categories c2
WHERE p.brand_id = b.id
AND (p.category_id = c.id or (p.category_id = c2.id and c2.parent_id = c.id))
AND brand_id = ? and c.parent_id = ? order by c.sort_order desc",brand_id,parent_id])
  end

  def self.find_by_brand(brand_id)
    self.find_by_sql(["SELECT DISTINCT c.*
FROM products p, categories c, brands b
WHERE p.brand_id = b.id
AND p.category_id = c.id
AND brand_id = ? order by c.sort_order desc",brand_id])
  end
end
 